using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Web.Caching;
using _18miljard.Config;
using _18miljard;

namespace _18miljard.Db
{
	enum ResultType
	{
		Scalar,
		SingleRow,
		MultiRow,
		NonQuery,
		Identity
	}

	public enum MiljardDb
	{
		Miljard,
		MiljardMail,
		MiljardAd,
		Temporary
	}

	public class Query
	{
		private OleDbConnection connection;
		private OleDbTransaction transaction = null;
		private QueryBuilder queryBuilder;
		private QueryArguments arguments;
		private bool batchMode = false;
		private static OleDbCommand identityCommand = new OleDbCommand("SELECT @@IDENTITY");

		public Query() // to be used for common queries, like GetUserId()
		{
		}

		public Query(string name)
			: this(name, (QueryArguments)null)
		{
        }

		public Query(string name, string argName, object argValue, OleDbType argType)
			: this(name)
		{
			this.arguments = new QueryArguments();

			this.arguments.Add(argName, argValue, argType);
		}
		
		public Query(string name, QueryArguments arguments)
		{
			this.arguments = arguments;

			this.queryBuilder = DbCache.GetQueryBuilder(name);
		}

		public object ExecuteBatch(OleDbTransaction transaction)
		{
			bool oldBatchMode = this.batchMode;

			this.transaction = transaction;

			this.batchMode = true;

			object obj = this.Execute();

			this.transaction = null;

			this.batchMode = oldBatchMode;

			return obj;
		}

		public object Execute(string argToReplace, string replaceBy)
		{
			StringBuilder commandString = this.queryBuilder.QueryString;

			commandString.Replace(argToReplace, replaceBy);

			return this.DoExecute(commandString);
		}

		/// <summary>
		/// Returns:
		/// - ResultSet if type = multirow or singlerow
		/// - object if type = scalar
		/// - int if type = nonquery or identity
		/// </summary>
		/// <returns></returns>
		public object Execute()
		{
			return this.DoExecute(this.queryBuilder.QueryString);
		}

		private object DoExecute(StringBuilder commandString)
		{
			OleDbCommand command = new OleDbCommand(commandString.ToString());
			command.CommandType = this.queryBuilder.CommandType;

			if (this.transaction != null)
				command.Transaction = this.transaction;

			if (this.arguments != null)
			{
				OleDbParameter[] args = new OleDbParameter[this.arguments.Count];

				this.arguments.CopyTo(args, 0);

				command.Parameters.AddRange(args);
			}

			object retVal;

			switch (this.queryBuilder.ResultType)
			{
				case ResultType.NonQuery: retVal = this.ExecuteNonQuery(command);
					break;
				case ResultType.Scalar: retVal = this.ExecuteScalar(command);
					break;
				case ResultType.Identity: retVal = this.ExecuteIdentity(command);
					break;
				default: retVal = this.ExecuteReader(command);
					break;
			}

			if (this.queryBuilder.ExpireCache != null)
			{
				HttpContext.Current.Cache.Remove(this.queryBuilder.ExpireCache);
			}

			if (retVal is System.DBNull)
				retVal = null;

			return retVal;
		}

		private ResultSet ExecuteReader(OleDbCommand command)
		{
			ResultSet resultSet = new ResultSet(this.queryBuilder.ResultSet.Columns);

			this.OpenConnection();

			command.Connection = this.connection;

			OleDbDataReader reader = command.ExecuteReader();

			object [] row = new object[resultSet.Columns.Count];

			while (reader.Read())
			{
				reader.GetValues(row);

				resultSet.AddRow(row);
			}

			reader.Close();

			this.CloseConnection();

			return resultSet;
		}

		private object ExecuteScalar(OleDbCommand command)
		{
			this.OpenConnection();

			command.Connection = this.connection;

			object retVal = command.ExecuteScalar();

			this.CloseConnection();

			return retVal;
		}

		private int ExecuteNonQuery(OleDbCommand command)
		{
			this.OpenConnection();

			command.Connection = this.connection;

			int rows = command.ExecuteNonQuery();

			this.CloseConnection();

			return rows;
		}

		private int ExecuteIdentity(OleDbCommand command)
		{
			int identity = -1;

			this.OpenConnection();

			Query.identityCommand.Connection = this.connection;

			command.Connection = this.connection;

			int rowsAffected = (int)(command.ExecuteNonQuery());

			if (rowsAffected > 0)
				identity = (int)Query.identityCommand.ExecuteScalar();

			this.CloseConnection();

			return identity;
		}

		internal void OpenConnection()
		{
			if (this.batchMode)
				return;

			ConnectionParameters connectionParameters = DbUtils.GetConnectionParameters(this.Db);

			if (this.connection == null)
			{
				this.connection = new OleDbConnection(connectionParameters.ConnectionString);
			}
			
			if (this.connection.State == System.Data.ConnectionState.Closed)
			{
				this.connection.Open();

				return;
			}

			if (this.connection.State == System.Data.ConnectionState.Broken)
			{
				this.connection.Close();

				this.connection.Open();

				return;
			}

			throw new Exception("Try to open an already open connection");
		}

		internal void CloseConnection()
		{
			if (this.batchMode)
				return;

			this.connection.Close();
		}

		internal OleDbConnection Connection
		{
			get { return this.connection; }
			set { this.connection = value; }
		}

		private MiljardDb Db
		{
			get
			{
				if (this.queryBuilder == null)
					return MiljardDb.Miljard;

				return this.queryBuilder.Db;
			}
		}
	}
}
